package com.xunwulian.base.util;

import com.google.gson.Gson;
import com.xunwulian.base.constant.UserStatus;
import com.xunwulian.base.constant.WorkStatus;
import com.xunwulian.base.entity.CardInfoEntity;
import com.xunwulian.base.entity.SmsGprsBalanceEntity;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    private Gson gson = new Gson();

    private final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    // excel工具类2
    private ExcelUtil2 excelUtil2 = new ExcelUtil2();

    /**
     * 通过传入excel文件的完整路径。然后解析excel文件
     *
     * @param path
     * @return
     */
    public Workbook loadExcel(String path) {
        return excelUtil2.loadExcel(path);
    }

    /**
     * workbook 转换为 CardInfoEntity
     *
     * @param workbook
     * @return
     */
    public List<CardInfoEntity> parseCardInfoExcel(Workbook workbook) {

        List<CardInfoEntity> cardList = new ArrayList<CardInfoEntity>();

        // excel文件里面有很多的sheet, 要遍历每一张sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {

            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }

            // 每张sheet里面有很多的row, 要遍历每行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                if (row.getCell(0) == null || !NumberUtil.isNumberic(getValue(row.getCell(0)).trim())) {
                    logger.error("msisdn非法：" + row.getCell(0));
                    continue;
                }

                // 每行都有很多列, 要遍历每列
                CardInfoEntity entity = new CardInfoEntity();

                /**
                 *  msisdn
                 */
                entity.setMsisdn(getValue(row.getCell(0)));
                cardList.add(entity);

                logger.info(gson.toJson(entity));
            }
        }

        return cardList;
    }

    /**
     * 移动公司格式的
     * workbook 转换为 CardInfoEntity
     *
     * @param workbook
     * @return
     */
    public List<CardInfoEntity> parseCMCCCardInfoExcel(Workbook workbook) {

        List<CardInfoEntity> cardList = new ArrayList<CardInfoEntity>();

        // excel文件里面有很多的sheet, 要遍历每一张sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {

            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }

            // 每张sheet里面有很多的row, 要遍历每行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                if (row.getCell(0) == null) {
                    continue;
                }
                // 每行都有很多列, 要遍历每列
                CardInfoEntity entity = new CardInfoEntity();

                /**
                 *  msisdn imsi iccid cardstatus workstatus gprs sms
                 */
                entity.setMsisdn(getValue(row.getCell(0)));
                entity.setImsi(getValue(row.getCell(2)));
                entity.setIccid(getValue(row.getCell(3)));

                logger.info("row.getCell(4):" + row.getCell(4) + ", row.getCell(5):" + row.getCell(5));
                if (row.getCell(4) != null) {
                    UserStatus cardStatus = EnumUtils.getEnumByDesc(UserStatus.class, getValue(row.getCell(4)));
                    entity.setStatus(cardStatus == null ? UserStatus.UNKNOWN.getType() : cardStatus
                            .getType());
                }

                if (row.getCell(5) != null) {
                    WorkStatus workStatus = EnumUtils.getEnumByDesc(WorkStatus.class, getValue(row.getCell(5)));
                    entity.setWorkStatus(workStatus == null ? WorkStatus.UNKNOWN.getType() : workStatus.getType());
                }

                entity.setGprs(getValue(row.getCell(6)));
                // TODO 这里要慎重
                entity.setSms(new Double(getValue(row.getCell(7))).longValue());

                cardList.add(entity);

                logger.info(gson.toJson(entity));
            }
        }

        return cardList;
    }


    /**
     * workbook 转换为 SmsGprsBalanceEntity
     *
     * @param workbook
     * @return
     */
    public List<SmsGprsBalanceEntity> parseGprsSmsExcel(Workbook workbook) {

        List<SmsGprsBalanceEntity> list = new ArrayList();

        // excel文件里面有很多的sheet, 要遍历每一张sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {

            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }

            // 每张sheet里面有很多的row, 要遍历每行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                // 每行都有很多列, 要遍历每列
                SmsGprsBalanceEntity entity = new SmsGprsBalanceEntity();
                entity.setDate(getValue(row.getCell(0)));
                entity.setMsisdn(getValue(row.getCell(1)));
                entity.setGprs(getValue(row.getCell(4)));
                entity.setSms(getValue(row.getCell(7)));

                logger.info(gson.toJson(entity));
            }
        }

        return list;
    }

    /**
     * 得到每个cell里面的值
     */
    private String getValue(Cell cell) {
        return excelUtil2.getValue(cell);
    }

    public <T> Workbook parseEntities(Collection<T> entities, Map<String, String> names) {
        return excelUtil2.parseEntities(entities, names);
    }

    public File writeToFile(Workbook workbook, String root, String path) throws IOException {
        return excelUtil2.writeToFile(workbook, root, path);
    }

    public File writeToFile(Workbook workbook, String path) throws IOException {
        return excelUtil2.writeToFile(workbook, path);
    }

    public File writeToFile(Workbook workbook, File file) throws IOException {
        return excelUtil2.writeToFile(workbook, file);
    }
}
